﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using V5_EaspLibs.DBUtility;
using Dapper;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Reflection;
using V5_EaspLibs.VAction;

namespace V5_EaspLibs.Core {
    /// <summary>
    /// 多种数据库帮助类
    /// </summary>
    public class DBHelper {
        private DataBaseType? _dbType;
        private string _dbLink;
        /// <summary>
        /// 数据库类型
        /// </summary>
        public DataBaseType? DbType {
            get {
                if (_dbType == null) {
                    return PubConstant.ConnectionDbType;
                }
                return _dbType;
            }
            set { _dbType = value; }
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public string DbLink {
            get {
                if (_dbType == null && string.IsNullOrEmpty(_dbLink)) {
                    return PubConstant.ConnectionString;
                }
                return _dbLink;
            }
            set { _dbLink = value; }
        }

        private static readonly DBHelper m_Instance = new DBHelper();
        /// <summary>
        /// 
        /// </summary>
        public static DBHelper Instance {
            get { return m_Instance; }
        }
        /// <summary>
        /// 
        /// </summary>
        public DBHelper() {
        }

        /// <summary>
        /// 创建新的数据库
        /// </summary>
        /// <returns></returns>
        public DBHelper CreateNewDb(DataBaseType dbType, string linkStr) {
            string dbPath = AppDomain.CurrentDomain.BaseDirectory;
            DBHelper db = new DBHelper();
            db.DbType = dbType;
            db.DbLink = string.Format(linkStr, dbPath);
            return db;
        }
        #region 基本方法
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string SQLString) {
            int result = 0;
            switch (this.DbType) {
                case DataBaseType.OleDb:
                    result = OleDbHelper.ExecuteNonQuery(
                        new OleDbConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.SqlServer:
                    result = SqlHelper.ExecuteNonQuery(
                        new SqlConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.Oracle:
                    break;
                case DataBaseType.SQLite:
                    break;
                case DataBaseType.MySql:
                    break;
            }
            return result;
        }
        /// <summary>
        /// 查询SQL语句
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet ExecuteQuery(string SQLString) {
            DataSet ds = new DataSet();
            switch (this.DbType) {
                case DataBaseType.OleDb:
                    ds = OleDbHelper.ExecuteQuery(
                        new OleDbConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.SqlServer:
                    ds = SqlHelper.ExecuteQuery(
                        new SqlConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.Oracle:
                    break;
                case DataBaseType.SQLite:
                    break;
                case DataBaseType.MySql:
                    break;
            }
            return ds;
        }
        /// <summary>
        /// 返回首行首列
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public object ExecuteScalar(string SQLString) {
            object o = null;
            switch (this.DbType) {
                case DataBaseType.OleDb:
                    o = OleDbHelper.ExecuteScalar(
                        new OleDbConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.SqlServer:
                    o = SqlHelper.ExecuteScalar(
                        new SqlConnection(this.DbLink), CommandType.Text, SQLString, null);
                    break;
                case DataBaseType.Oracle:
                    break;
                case DataBaseType.SQLite:
                    break;
                case DataBaseType.MySql:
                    break;
            }
            return o;
        }
        #endregion

        #region 扩展方法 SQL

        /// <summary>
        /// 添加数据
        /// </summary>
        /// <param name="from">表名称</param>
        /// <param name="attributes">数据  new{id=1,title="test1",createtime=DateTime.Now,isshow=true}</param>
        /// <returns></returns>
        public int Insert(string from, object attributes) {
            this.Reset();
            if (attributes != null) {
                PropertyInfo[] pp = attributes.GetType().GetProperties();
                foreach (PropertyInfo p in pp) {
                    string value = p.GetValue(attributes, null).ToString();
                    string name = p.Name;
                    string type = p.PropertyType.ToString();
                    if (!string.IsNullOrEmpty(value)) {
                        RowTypeEnum rt = RowTypeEnum.String;
                        if (type == "System.Int32") {
                            rt = RowTypeEnum.Int32;
                        }
                        this.AddFieldItem(name, value, rt);
                    }
                }
            }
            return this.Insert(from, this._alFieldItems);
        }
        /// <summary>
        /// 拼装字段串
        /// </summary>
        /// <param name="dbType"></param>
        /// <param name="dbValue"></param>
        /// <returns></returns>
        private string GetColumnValue(string dbType, string dbValue) {
            string temp = string.Empty;
            switch (dbType) {
                case "System.Int32":
                    temp = dbValue;
                    break;
                case "System.String":
                    temp = "'" + dbValue + "'";
                    break;
                case "System.DateTime":
                    temp = "'" + dbValue + "'";
                    break;
                case "System.Boolean":
                    temp = "'" + dbValue + "'";
                    break;
            }
            return temp;
        }
        /// <summary>
        /// 修改数据
        /// </summary>
        /// <param name="from">表名称</param>
        /// <param name="attributes">new{id=1,title="test1",createtime=DateTime.Now,isshow=true}</param>
        /// <param name="where">查询条件  id=1</param>
        /// <returns></returns>
        public int Update(string from, object attributes, string where) {
            this.Reset();
            if (attributes != null) {
                PropertyInfo[] pp = attributes.GetType().GetProperties();
                foreach (PropertyInfo p in pp) {
                    string value = p.GetValue(attributes, null).ToString();
                    string name = p.Name;
                    string type = p.PropertyType.ToString();
                    if (!string.IsNullOrEmpty(value)) {
                        RowTypeEnum rt = RowTypeEnum.String;
                        if (type == "System.Int32") {
                            rt = RowTypeEnum.Int32;
                        }
                        this.AddFieldItem(name, value, rt);
                    }
                }
            }
            return this.Update(from, this._alFieldItems, where);
        }
        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="from">表名称</param>
        /// <param name="where">查询条件  id=1</param>
        /// <returns></returns>
        public int Delete(string from, string where) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Delete from " + from + " ");
            strSql.Append(" Where " + where);
            return this.ExecuteNonQuery(strSql.ToString());
        }
        /// <summary>
        /// 查询数据
        /// </summary>
        /// <param name="what">字段</param>
        /// <param name="from">表名称</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="limit">显示个数</param>
        /// <returns></returns>
        public DataSet Query_By(string what, string from, string where, string orderby, int limit) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select ");
            if (limit < 0) {
                limit = 10;
            }
            if (limit > 0) {
                if (this.DbType == DataBaseType.OleDb || this.DbType == DataBaseType.SqlServer) {
                    strSql.Append(" Top " + limit + " ");
                }
            }
            if (string.IsNullOrEmpty(what)) {
                what = "*";
            }
            strSql.Append(" " + what + " from " + from + " where 1=1 ");
            if (!string.IsNullOrEmpty(where)) {
                strSql.Append(" and " + where + " ");
            }
            if (!string.IsNullOrEmpty(orderby)) {
                strSql.Append(" order by " + orderby + " ");
            }
            if (limit > 0) {
                if (this.DbType == DataBaseType.MySql || this.DbType == DataBaseType.SQLite) {
                    strSql.Append(" limit " + limit + " ");
                }
            }
            return this.ExecuteQuery(strSql.ToString());
        }
        /// <summary>
        /// 分页查询记录
        /// </summary>
        /// <param name="what">字段</param>
        /// <param name="from">表名称</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderby">排序</param>
        /// <param name="limit">显示个数</param>
        /// <param name="pageIndex">当前索引</param>
        /// <param name="recordCount">记录个数</param>
        /// <returns></returns>
        public DataSet Query_Pager(string what, string from, string where, string orderby, int limit, int pageIndex, ref int recordCount) {
            if (pageIndex < 1) {
                pageIndex = 1;
            }
            StringBuilder strSql1 = new StringBuilder();
            strSql1.Append("select count(*) from " + from + " ");
            if (!string.IsNullOrEmpty(where)) {
                strSql1.Append(" where " + where + " ");
            }
            recordCount = Convert.ToInt32("0" + this.ExecuteScalar(strSql1.ToString()));
            int startIndex = limit * (pageIndex - 1);
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (limit < 1) {
                limit = 10;
            }
            if (this.DbType == DataBaseType.OleDb || this.DbType == DataBaseType.SqlServer) {
                strSql.Append(" top " + limit + " ");
            }
            if (string.IsNullOrEmpty(what)) {
                what = "*";
            }
            strSql.Append(" " + what + " from " + from + " where 1=1 ");
            if (!string.IsNullOrEmpty(where)) {
                strSql.Append(" and " + where + " ");
            }
            if (this.DbType == DataBaseType.OleDb || this.DbType == DataBaseType.SqlServer) {
                //startIndex = startIndex + limit;
                if (startIndex > 0) {
                    strSql.Append(" and id not in(select top " + startIndex + " id from " + from + " where 1=1 ");
                    if (!string.IsNullOrEmpty(where)) {
                        strSql.Append(" and " + where + " ");
                    }
                    if (!string.IsNullOrEmpty(orderby)) {
                        strSql.Append(" order by " + orderby + " ");
                    }
                    strSql.Append(" ) ");
                }
            }
            if (!string.IsNullOrEmpty(orderby)) {
                strSql.Append(" order by " + orderby + " ");
            }
            if (limit > 0) {
                if (this.DbType == DataBaseType.MySql || this.DbType == DataBaseType.SQLite) {
                    strSql.Append(" limit " + startIndex + "," + limit + " ");
                }
            }
            return this.ExecuteQuery(strSql.ToString());
        }
        #endregion

        #region 1
        private List<VDataRow> _alFieldItems = new List<VDataRow>();
        /// <summary>
        /// 重置数据
        /// </summary>
        public void Reset() {
            this._alFieldItems.Clear();
        }
        /// <summary>
        /// 添加一个字段/值对到数组中。
        /// </summary>
        /// <param name="_fieldName">字段名称。</param>
        /// <param name="_fieldValue">字段值。</param>
        public void AddFieldItem(string _fieldName, object _fieldValue) {
            Type sType = _fieldValue.GetType();
            RowTypeEnum rt = RowTypeEnum.String;
            if (sType.ToString() == "System.Int32") {
                rt = RowTypeEnum.Int32;
            }
            this._alFieldItems.Add(new VDataRow() { RowName = _fieldName, RowType = rt, RowValue = _fieldValue.ToString() });
        }
        /// <summary>
        /// 添加一个字段/值对到数组中。
        /// </summary>
        /// <param name="_fieldName"></param>
        /// <param name="_fieldValue"></param>
        /// <param name="rt"></param>
        public void AddFieldItem(string _fieldName, object _fieldValue, RowTypeEnum rt) {
            this._alFieldItems.Add(new VDataRow() { RowName = _fieldName, RowType = rt, RowValue = _fieldValue.ToString() });
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="from">表</param>
        /// <param name="drList">数据列表</param>
        /// <returns></returns>
        public int Insert(string from, List<VDataRow> drList) {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql1 = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            foreach (VDataRow vdr in drList) {
                strSql1.Append("" + vdr.RowName + ",");
                if (vdr.RowType == RowTypeEnum.Int32) {
                    strSql2.Append("" + vdr.RowValue + ",");
                }
                else {
                    strSql2.Append("'" + vdr.RowValue + "',");
                }
            }
            strSql.Append("insert into " + from + "(");
            strSql.Append(strSql1.ToString().Remove(strSql1.Length - 1));
            strSql.Append(")");
            strSql.Append(" values (");
            strSql.Append(strSql2.ToString().Remove(strSql2.Length - 1));
            strSql.Append(")");
            return this.ExecuteNonQuery(strSql.ToString());
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="from">表名称</param>
        /// <param name="drList">数据列表</param>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public int Update(string from, List<VDataRow> drList, string where) {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + from + " set ");
            foreach (VDataRow vdr in drList) {
                if (vdr.RowType == RowTypeEnum.Int32) {
                    strSql.Append("" + vdr.RowName + "=" + vdr.RowValue + ",");
                }
                else {
                    strSql.Append("" + vdr.RowName + "='" + vdr.RowValue + "',");
                }
            }
            int n = strSql.ToString().LastIndexOf(",");
            strSql.Remove(n, 1);
            strSql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(where)) {
                strSql.Append(" and " + where);
            }
            return this.ExecuteNonQuery(strSql.ToString());
        }
        /// <summary>
        /// 插入数据
        /// </summary>
        /// <param name="strFrom"></param>
        /// <returns></returns>
        public int Insert(string strFrom) {
            return this.Insert(strFrom, this._alFieldItems);
        }
        /// <summary>
        /// 更新数据
        /// </summary>
        /// <param name="strFrom"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(string strFrom, string where) {
            return this.Update(strFrom, this._alFieldItems, where);
        }
        #endregion

        #region 2
        /// <summary>
        /// 
        /// </summary>
        /// <param name="FieldName"></param>
        /// <param name="TableName"></param>
        /// <returns></returns>
        public int GetMaxId(string FieldName, string TableName) {
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;
            object obj = this.ExecuteScalar(strsql);
            if (obj == null || obj == DBNull.Value) {
                return 1;
            }
            else {
                return int.Parse(obj.ToString());
            }
        }
        #endregion


        #region 存储过程
        /// <summary>
        /// 执行存储过程 返回影响的行数
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="rowsAffected"></param>
        /// <returns></returns>
        public int RunProcedure(string storedProcName, IDataParameter[] parameters, out int rowsAffected) {
            int result = 0;
            if (this.DbType == DataBaseType.SqlServer) {
                result = DbHelperSQL.RunProcedure(storedProcName, parameters, out rowsAffected);
            }
            else {
                throw new Exception("不支持存储过程!");
            }
            return result;
        }
        /// <summary>
        /// 执行存储过程 只读连接数据库数据
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public IDataReader RunProcedure(string storedProcName, IDataParameter[] parameters) {
            IDataReader reader;
            if (this.DbType == DataBaseType.SqlServer) {
                reader = DbHelperSQL.RunProcedure(storedProcName, parameters);
            }
            else {
                throw new Exception("不支持存储过程!");
            }
            return reader;
        }
        /// <summary>
        /// 执行存储过程 返回数据集
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName) {
            DataSet ds = new DataSet();
            if (this.DbType == DataBaseType.SqlServer) {
                ds = DbHelperSQL.RunProcedure(storedProcName, parameters, tableName);
            }
            else {
                throw new Exception("不支持存储过程!");
            }
            return ds;
        }
        /// <summary>
        /// 执行存储过程 返回数据集
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="Times"></param>
        /// <returns></returns>
        public DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName, int Times) {
            DataSet ds = new DataSet();
            if (this.DbType == DataBaseType.SqlServer) {
                ds = DbHelperSQL.RunProcedure(storedProcName, parameters, tableName, Times);
            }
            else {
                throw new Exception("不支持存储过程!");
            }
            return ds;
        }
        #endregion
    }
}
